package hutech.ksv.dao;

import hutech.ksv.entity.ChuNhaTroEntity;
import hutech.ksv.entity.ChuQuanAnEntity;
import hutech.ksv.entity.CongTyTuyenDungEntity;
import hutech.ksv.entity.QuanAnEntity;
import hutech.ksv.entity.SinhVienEntity;
import hutech.ksv.entity.TaiKhoanSVEntity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class CapNhatThongTinThanhVienDao implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 5500711781040847915L;
	
	@Autowired
	DataSource dataSource;
	
	public void updateSinhVien(SinhVienEntity sinhVienEntity) {
		String query = "UPDATE `sinhvien` " 
				+ "SET `TenSV`=?,"
				+ "`DiaChiSV`=?,`NgaySinhSV`=?,"
				+ "`TruongSV`=?,`DiaChiTruongSV`=?, "
				+ "`MSSV`=?, `SDTSV`=?, "
				+ "`GioiTinhSV`=?, `EmailSV`=? " 
				+ "WHERE `MaSV`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			preparedStatement.setString(1, sinhVienEntity.getTenSV());
			preparedStatement.setString(2, sinhVienEntity.getDiaChiSV());
			preparedStatement.setDate(3, sinhVienEntity.getNgaySinhSV());
			preparedStatement.setString(4, sinhVienEntity.getTruongSV());
			preparedStatement.setString(5, sinhVienEntity.getDiaChiTruongSV());
			preparedStatement.setString(6, sinhVienEntity.getMssv());
			preparedStatement.setString(7, sinhVienEntity.getSdtSV());
			preparedStatement.setString(8, sinhVienEntity.getGioiTinhSV());
			preparedStatement.setString(9, sinhVienEntity.getEmailSV());
			preparedStatement.setInt(10, sinhVienEntity.getMaSV());
			
			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void updateChuNhaTro(ChuNhaTroEntity chuNhaTroEntity) {
		String query = "UPDATE `ChuNhaTro` "
				+ "SET `TenCNT`=?, "
				+ "`DiaChiCNT`= ?, "
				+ "`GioiTinhCNT`=?, "
				+ "`SDTCNT`=?, "
				+ "`EmailCNT`=? "
				+ "WHERE `MaCNT`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			preparedStatement.setString(1, chuNhaTroEntity.getTenCNT());
			preparedStatement.setString(2, chuNhaTroEntity.getDiaChiCNT());
			preparedStatement.setString(3, chuNhaTroEntity.getGioiTinhCNT());
			preparedStatement.setString(4, chuNhaTroEntity.getSdtCNT());
			preparedStatement.setString(5, chuNhaTroEntity.getEmailCNT());
			preparedStatement.setInt(6, chuNhaTroEntity.getMaCNT());
			
			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void updateChuQuanAn(ChuQuanAnEntity chuQuanAnEntity) {
		String query = "UPDATE `ChuQuanAn` "
				+ "SET `TenCQA`=?, "
				+ "`DiaChiCQA`= ?, "
				+ "`SDTCQA`=?, "
				+ "`GioiTinhCQA`=?, "
				+ "`EmailCQA`=? "
				+ "WHERE `MaCQA`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			preparedStatement.setString(1, chuQuanAnEntity.getTenCQA());
			preparedStatement.setString(2, chuQuanAnEntity.getDiaChiCQA());
			preparedStatement.setString(3, chuQuanAnEntity.getSdtCQA());
			preparedStatement.setString(4, chuQuanAnEntity.getGioiTinhCQA());			
			preparedStatement.setString(5, chuQuanAnEntity.getEmailCQA());
			preparedStatement.setInt(6, chuQuanAnEntity.getMaCQA());
			
			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void updateCongTy(CongTyTuyenDungEntity congTyEntity) {
		String query = "UPDATE `congtytuyendung` "
				+ "SET `TenCT`=?, "
				+ "`DiaChiCT`= ?, "
				+ "`SDTCT`=?, "
				+ "`EmailCT`=? "
				+ "WHERE `MaCTTD`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			preparedStatement.setString(1, congTyEntity.getTenCT());
			preparedStatement.setString(2, congTyEntity.getDiaChiCT());
			preparedStatement.setString(3, congTyEntity.getSdtCT());
			preparedStatement.setString(4, congTyEntity.getEmailCT());
			preparedStatement.setInt(5, congTyEntity.getMaCTTD());
			
			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public SinhVienEntity getSinhVienTheoMa(int ma) {
		String query = "SELECT * FROM `sinhvien` WHERE `MaSV` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			
			preparedStatement.setInt(1, ma);
			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				SinhVienEntity tk = new SinhVienEntity();
				tk.setTenSV(rs.getString("TenSV"));
				tk.setDiaChiSV(rs.getString("DiaChiSV"));
				tk.setNgaySinhSV(rs.getDate("NgaySinhSV"));
				tk.setTruongSV(rs.getString("TruongSV"));
				tk.setDiaChiTruongSV(rs.getString("DiaChiTruongSV"));
				tk.setMssv(rs.getString("MSSV"));
				tk.setSdtSV(rs.getString("SDTSV"));
				tk.setGioiTinhSV(rs.getString("GioiTinhSV"));
				tk.setEmailSV(rs.getString("EmailSV"));
				return tk;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public ChuQuanAnEntity getChuQuanAnTheoMa(int ma) {
		String query = "SELECT * FROM `chuquanan` WHERE `MaCQA` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			
			preparedStatement.setInt(1, ma);
			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				ChuQuanAnEntity tk = new ChuQuanAnEntity();
				tk.setTenCQA(rs.getString("TenCQA"));
				tk.setDiaChiCQA(rs.getString("DiaChiCQA"));
				tk.setSdtCQA(rs.getString("SDTCQA"));
				tk.setGioiTinhCQA(rs.getString("GioiTinhCQA"));
				tk.setEmailCQA(rs.getString("EmailCQA"));
				return tk;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public ChuNhaTroEntity getChuNhaTroTheoMa(int ma) {
		String query = "SELECT * FROM `chunhatro` WHERE `MaCNT` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			
			preparedStatement.setInt(1, ma);
			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				ChuNhaTroEntity tk = new ChuNhaTroEntity();
				tk.setTenCNT(rs.getString("TenCNT"));
				tk.setDiaChiCNT(rs.getString("DiaChiCNT"));
				tk.setSdtCNT(rs.getString("SDTCNT"));
				tk.setGioiTinhCNT(rs.getString("GioiTinhCNT"));
				tk.setEmailCNT(rs.getString("EmailCNT"));
				return tk;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public CongTyTuyenDungEntity getCongTyTheoMa(int ma) {
		String query = "SELECT * FROM `congtytuyendung` WHERE `MaCTTD` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			
			preparedStatement.setInt(1, ma);
			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				CongTyTuyenDungEntity tk = new CongTyTuyenDungEntity();
				tk.setTenCT(rs.getString("TenCT"));
				tk.setDiaChiCT(rs.getString("DiaChiCT"));
				tk.setSdtCT(rs.getString("SDTCT"));
				tk.setEmailCT(rs.getString("EmailCT"));
				return tk;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

}
